package com.example.deliveryapplication.sql

import androidx.room.*
import com.example.deliveryapplication.entity.BearingModel
import com.example.deliveryapplication.entity.Client
import com.example.deliveryapplication.entity.Plan
import com.example.deliveryapplication.entity.PlanWithPlanItem

@Dao
interface PlanDao {
    @Insert
    fun insertChart(bearingModel: Plan): Long
    @Query("select * from `Plan`")
    fun loadAllChart(): List<Plan>
    @Query("select * from `Plan` where id=:planId")
    fun loadPlanById(planId:String): Plan
    @Update
    fun updatePlan(bean:Plan)
    @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH)
    @Transaction
    @Query("SELECT `Plan`.*, COUNT(PlanItem.planItemId) AS itemCount, COUNT(DISTINCT PlanItem.clientId) AS clientCount FROM `Plan` LEFT JOIN PlanItem ON `Plan`.id = PlanItem.planId GROUP BY `Plan`.id ORDER BY `Plan`.insertTime DESC")
    //@Query("SELECT COUNT(*) AS itemCount, COUNT(DISTINCT PlanItem.clientId) AS clientCount FROM `Plan` LEFT JOIN PlanItem ON `Plan`.id = PlanItem.planId")
    //@Query("SELECT `Plan`.*, COUNT(PlanItem.planItemId) AS itemCount, subquery.clientCount FROM `Plan` LEFT JOIN PlanItem ON `Plan`.id = PlanItem.planId LEFT JOIN (SELECT clientId, COUNT(*) AS clientCount FROM `PlanItem` GROUP BY clientId ) AS subquery ON `PlanItem`.clientId = subquery.clientId GROUP BY `Plan`.id ORDER BY `Plan`.insertTime DESC")
   // @Query("SELECT `Plan`.* , COUNT(PlanItem.planItemId) AS itemCount  FROM `Plan` LEFT JOIN PlanItem ON `Plan`.id = PlanItem.planId GROUP BY `Plan`.id Order by `Plan`.insertTime DESC")
    fun getPlansWithItemCount(): List<PlanWithPlanItem>
    @Delete
    fun deletePlan(bean:Plan):Int
}